﻿Namespace Saglik.Gozluk
	Module Functions

		Function Oku(Token As Token, repsube As Integer, dosyano As Integer) As Recordset
			Return Token.OpenSQL("select * from sgozluk where repsube = @repsube and dosyano = @dosyano", repsube, dosyano)
		End Function

		Function OdemeIstatistigi(ByVal Process As Token, ByVal Donem1 As String, ByVal Donem2 As String) As Recordset
			Q("select durum,yasa,uyruk,bolge,sum(miktar) as miktar,sum(adet) as adet,sum(cerceve) as cerceve,sum(cerodemik) as cerodemik,sum(camsayi) as camsayi, sum(camodemik) as camodemik from (")
			Q("select case when sno>10000000 then 'SGY' else 'SSY' end as yasa,case when kimlik between '000000' and '399999' and len(kimlik) <= 6 then '1' else '2' end as uyruk,")
			Q("sdrm as durum, sube as bolge,sum(odememik) as miktar, 1 as adet,sum(cerceve) as cerceve,sum(cerodemik) as cerodemik,")
			Q("sum(camsayi) as camsayi, sum(camodemik) as camodemik")
			Q("from ssdmerkez..sgozluk where onay = 'E' and convert(char(6),odemetar,112) between :~donem1 and :~donem2 group by kimlik, sno, sdrm, sube) t group by durum,bolge,yasa,uyruk")
			Return Process.OpenSQL("*", Donem1, Donem2)
		End Function

		Function OdemeDonemleri() As String
			Return "q@select distinct convert(char(6),odemetar,112) as kod, convert(char(7),odemetar,111) as ad from ssdmerkez..sgozluk order by 1 desc"
		End Function

		Function OkuKimlikle(Token As Token, kimlik As String, uzakyakin As String) As Recordset
			Return Token.OpenSQL("select * from ssdmerkez.dbo.sgozluk where kimlik = @kimlik and cam = @cam order by murtar desc", kimlik, uzakyakin)
		End Function

		Sub SilKimlikle(Token As Token, kimlik As String, uzakyakin As String)
			Token.ExecSQL("delete from ssdmerkez.dbo.sgozluk where kimlik = @kimlik and cam = @cam", kimlik, uzakyakin)
		End Sub

		Sub Duzelt(Token As Token, kimlik As String, uzakyakin As String, dosyano As String, sube As String, cerceve As Decimal, cam As Decimal, toplam As Decimal)
			Dim Rs As Recordset = OkuKimlikle(Token, kimlik, uzakyakin)
			Rs.SetValues("dosyano,sube,cerodemik,camodemik,odememik", dosyano, sube, cerceve, cam, toplam)
			Rs.Save()
		End Sub

		Function Liste(ByVal Token As Token, ByVal d1 As Date, ByVal d2 As Date, ByVal RaporTip As Integer) As Recordset
			If RaporTip = 1 Then
				Q("select sdrm,sube,sno,kimlik,adi,soyadi,camodemik,cerodemik,camsayi,")
				Q("case when len(ltrim(sno)) < 9 then 'SSY' else 'SGY' end as yasa,isnull(cam,'') as cam,")
				Q("case when isnull(cerceve,0) > 0 then 'E' else 'H' end as cercevetur,")
				Q("convert(datetime,convert(char(8),murtar,112)) as murtar,")
				Q("convert(datetime,convert(char(8),islemtar,112)) as islemtar")
				Q("from ssdmerkez..sgozluk where islemtar between @d1 and dateadd(y,1,@d2)")
				Return Token.OpenSQL("*", d1, d2)
			ElseIf RaporTip = 2 Then
				Q("select sdrm,sube,sno,kimlik,adi,soyadi,camodemik,cerodemik,camsayi,")
				Q("case when len(ltrim(sno)) < 9 then 'SSY' else 'SGY' end as yasa,isnull(cam,'') as cam,")
				Q("case when isnull(cerceve,0) > 0 then 'E' else 'H' end as cercevetur,")
				Q("convert(datetime,convert(char(8),murtar,112)) as murtar,")
				Q("convert(datetime,convert(char(8),islemtar,112)) as islemtar")
				Q("from ssdmerkez..sgozluk where islemtar between @d1 and dateadd(y,1,@d2)")
				Return Token.OpenSQL("*", d1, d2)
			ElseIf RaporTip = 3 Then
				Q("select  kimlik,sno,sdrm,sube,min(adi) as adi,min(soyadi) as soyadi, ")
				Q("case when len(ltrim(sno)) < 9 then 'SSY' else 'SGY' end as yasa, ")
				Q("convert(datetime,convert(char(8),MIN(murtar),112)) as murtar, ")
				Q("convert(datetime,convert(char(8),min(islemtar),112)) as islemtar ")
				Q("from ssdmerkez..sgozluk where islemtar between @d1 and dateadd(y,1,@d2) ")
				Q("group by kimlik, sno, sdrm, sube")
				Return Token.OpenSQL("*", d1, d2)
			End If
			Return Nothing
		End Function

		Function Muracaatlar(Token As Token) As Recordset
			Q("select repsube,dosyano,sdrm,kimlik,sno,murtar,islemtar,")
			Q("rtrim(adi)+' '+rtrim(soyadi) as adi,rtrim(hturu)+'-'+ltrim(kararno) as hesapkarar")
			Q("from sgozluk where isnull(onay,'') = ''")
			Q("and murtar >= '20120101'")
			Q("order by islemtar desc")
			Return Token.OpenSQL("*")
		End Function

	End Module
End Namespace
